'use strict';

const Service = require('egg').Service;
const Sequelize = require('sequelize');
const Op = Sequelize.Op;

class StarService extends Service {
    // 歌星列表
    async page(query) {
      const { ctx } = this;
      let config = {
        where:{
          isDel:0,
          status:1
        },
        order:[
          ['createTime', 'DESC']
        ],
        attributes: {
          include: [
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM album
                  WHERE
                      album.sid = star.id
                      AND
                      album.isDel = 0
                      AND
                      album.status = 1
              )`),
              'albumNum'
            ],
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM music
                  WHERE
                      music.sid = star.id
                      AND
                      music.isDel = 0
                      AND
                      music.status = 1
              )`),
              'musicNum'
            ],
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM collection
                  WHERE
                      collection.cid = star.id
                      AND
                      collection.type = 2
              )`),
              'collectionNum'
            ],
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM likes
                  WHERE
                      likes.lid = star.id
                      AND
                      likes.type = 2
              )`),
              'likesNum'
            ],
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM comment
                  WHERE
                      comment.cid = star.id
                      AND
                      comment.type = 2
                      AND
                      comment.isDel = 0
                      AND
                      comment.status = 1
              )`),
              'commentNum'
            ],
            [
              Sequelize.literal(`(
                    SELECT SUM(music.playNum)
                    FROM music
                    WHERE
                        music.sid = star.id
                        AND
                        music.isDel = 0
                        AND
                        music.status = 1
                )`),
              'playNum'
            ]
          ],
          exclude: ['isDel','delTime','status'],
        }
      };
      if(ctx.request.header.uid){
        config.attributes.include.push([
          Sequelize.literal(`(
            SELECT COUNT(*)
            FROM collection
            WHERE
              collection.cid = star.id
              AND
              collection.type = 2
              AND
              collection.uid = ${ctx.request.header.uid}
          )`),
          'isCollection'
        ]);
        config.attributes.include.push([
          Sequelize.literal(`(
            SELECT COUNT(*)
            FROM likes
            WHERE
                likes.lid = star.id
                AND
                likes.type = 2
                AND
                likes.uid = ${ctx.request.header.uid}
          )`),
          'isLikes'
        ]);
      }
      if(query.keyword){
        config.where.name = {
          [Op.like]:`%${query.keyword}%`
        }
      }
      return await ctx.model.Star.findAll(config);
    }

    // 歌星详情
    async detail(query) {
      const { ctx } = this;
      let config = {
        where:{
          isDel:0,
          id:query.id,
          status:1
        },
        attributes: {
          include: [
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM album
                  WHERE
                      album.sid = star.id
                      AND
                      album.isDel = 0
                      AND
                      album.status = 1
              )`),
              'albumNum'
            ],
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM music
                  WHERE
                      music.sid = star.id
                      AND
                      music.isDel = 0
                      AND
                      music.status = 1
              )`),
              'musicNum'
            ],
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM collection
                  WHERE
                      collection.cid = star.id
                      AND
                      collection.type = 2
              )`),
              'collectionNum'
            ],
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM likes
                  WHERE
                      likes.lid = star.id
                      AND
                      likes.type = 2
              )`),
              'likesNum'
            ],
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM comment
                  WHERE
                      comment.cid = star.id
                      AND
                      comment.type = 2
                      AND
                      comment.isDel = 0
                      AND
                      comment.status = 1
              )`),
              'commentNum'
            ],
            [
              Sequelize.literal(`(
                    SELECT SUM(music.playNum)
                    FROM music
                    WHERE
                        music.sid = star.id
                        AND
                        music.isDel = 0
                        AND
                        music.status = 1
                )`),
              'playNum'
            ]
          ],
          exclude: ['isDel','delTime','status']
        }
      };
      if(ctx.request.header.uid){
        config.attributes.include.push([
          Sequelize.literal(`(
            SELECT COUNT(*)
            FROM collection
            WHERE
              collection.cid = star.id
              AND
              collection.type = 2
              AND
              collection.uid = ${ctx.request.header.uid}
          )`),
          'isCollection'
        ]);
        config.attributes.include.push([
          Sequelize.literal(`(
            SELECT COUNT(*)
            FROM likes
            WHERE
                likes.lid = star.id
                AND
                likes.type = 2
                AND
                likes.uid = ${ctx.request.header.uid}
          )`),
          'isLikes'
        ]);
      }
      return await ctx.model.Star.findOne(config);
    }

    // 歌星评论列表
    async commentPage(query) {
      const { ctx } = this;
      let config = {
        where:{
          isDel:0,
          cid:query.cid,
          type:2,
          status:1
        },
        order:[
          ['createTime', 'DESC']
        ],
        attributes: {
          include: [
            [
              Sequelize.literal(`(
                    SELECT COUNT(*)
                    FROM likes
                    WHERE
                        likes.lid = comment.id
                        AND
                        likes.type = 5
                )`),
              'likesNum'
            ],
            [
              Sequelize.literal(`(
                    SELECT COUNT(*)
                    FROM comment AS c
                    WHERE
                        c.pid = comment.id
                        AND
                        c.type = 2
                        AND
                        c.isDel = 0
                        AND
                        c.status = 1
                )`),
              'commentNum'
            ],
            [Sequelize.col('user.nickname'), 'nickname']
          ],
          exclude: ['isDel','delTime','type','status','uid'],
        },
        include: [{
          model: ctx.model.User,
          as: 'user',
          duplicating:false,
          required:false,
          attributes: []
        }],
        distinct: true
      };
      if(ctx.request.header.uid){
        config.attributes.include.push([
          Sequelize.literal(`(
            SELECT COUNT(*)
            FROM likes
            WHERE
                likes.lid = comment.id
                AND
                likes.type = 5
                AND
                likes.uid = ${ctx.request.header.uid}
          )`),
          'isLikes'
        ]);
      }
      config.where.pid = query.pid?query.pid:0;
      if(!query.pageNO){
        query.pageNO = 1;
      }
      if(!query.pageSize){
        query.pageSize = 10;
      }
      config.offset = (query.pageNO-1)*query.pageSize;
      config.limit = query.pageSize;
      return await ctx.model.Comment.findAndCountAll(config);
    }

    // 歌星评论/回复
    async comment(data) {
      const { ctx } = this;
      return await ctx.model.Comment.create({
        cid:data.cid,
        pid:data.pid?data.pid:0,
        content:data.content,
        type:2,
        uid:ctx.request.header.uid,
        createTime:new Date().getTime(),
        updateTime:new Date().getTime(),
        delTime:0
      });
    }

    // 歌星评论是否已点赞
    async isCommentLikes(data){
      const { ctx } = this;
      let config = {
        where:{
          lid:data.lid,
          type:5,
          uid:ctx.request.header.uid
        },
        attributes: ['id']
      };
      return await ctx.model.Likes.findOne(config);
    }

    // 歌星评论点赞
    async doCommentLikes(data) {
      const { ctx } = this;
      return await ctx.model.Likes.create({
        lid:data.lid,
        type:5,
        uid:ctx.request.header.uid,
        createTime:new Date().getTime()
      });
    }

    // 歌星评论取消点赞
    async doClearCommentLikes(data) {
      const { ctx } = this;
      return await ctx.model.Likes.destroy({
        where:{
          lid:data.lid,
          type:5,
          uid:ctx.request.header.uid
        }
      });
    }

    // 歌星是否已点赞
    async isLikes(data){
      const { ctx } = this;
      let config = {
        where:{
          lid:data.lid,
          type:2,
          uid:ctx.request.header.uid
        },
        attributes: ['id']
      };
      return await ctx.model.Likes.findOne(config);
    }

    // 歌星点赞
    async doLikes(data) {
      const { ctx } = this;
      return await ctx.model.Likes.create({
        lid:data.lid,
        type:2,
        uid:ctx.request.header.uid,
        createTime:new Date().getTime()
      });
    }

    // 歌星取消点赞
    async doClearLikes(data) {
      const { ctx } = this;
      return await ctx.model.Likes.destroy({
        where:{
          lid:data.lid,
          type:2,
          uid:ctx.request.header.uid
        }
      });
    }

    // 歌星是否已收藏
    async isCollection(data){
      const { ctx } = this;
      let config = {
        where:{
          cid:data.cid,
          type:2,
          uid:ctx.request.header.uid
        },
        attributes: ['id']
      };
      return await ctx.model.Collection.findOne(config);
    }

    // 歌星收藏
    async doCollection(data) {
      const { ctx } = this;
      return await ctx.model.Collection.create({
        cid:data.cid,
        type:2,
        uid:ctx.request.header.uid,
        createTime:new Date().getTime()
      });
    }

    // 歌星取消收藏
    async doClearCollection(data) {
      const { ctx } = this;
      return await ctx.model.Collection.destroy({
        where:{
          cid:data.cid,
          type:2,
          uid:ctx.request.header.uid
        }
      });
    }

    // 收藏的歌星
    async collectionPage(query) {
      const { ctx } = this;
      let collectionResult = await ctx.model.Collection.findAll({
        where:{
          type:2,
          uid:ctx.request.header.uid
        },
        attributes: ['cid']
      });
      let collectionList = [];
      for(let i of collectionResult){
        collectionList.push(i.cid);
      }
      let config = {
        where:{
          isDel:0,
          status:1,
          id: {
            [Op.in]:collectionList
          }
        },
        order:[
          ['createTime', 'DESC']
        ],
        attributes: {
          include: [
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM album
                  WHERE
                      album.sid = star.id
                      AND
                      album.isDel = 0
                      AND
                      album.status = 1
              )`),
              'albumNum'
            ],
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM music
                  WHERE
                      music.sid = star.id
                      AND
                      music.isDel = 0
                      AND
                      music.status = 1
              )`),
              'musicNum'
            ],
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM collection
                  WHERE
                      collection.cid = star.id
                      AND
                      collection.type = 2
              )`),
              'collectionNum'
            ],
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM likes
                  WHERE
                      likes.lid = star.id
                      AND
                      likes.type = 2
              )`),
              'likesNum'
            ],
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM comment
                  WHERE
                      comment.cid = star.id
                      AND
                      comment.type = 2
                      AND
                      comment.isDel = 0
                      AND
                      comment.status = 1
              )`),
              'commentNum'
            ],
            [
              Sequelize.literal(`(
                    SELECT SUM(music.playNum)
                    FROM music
                    WHERE
                        music.sid = star.id
                        AND
                        music.isDel = 0
                        AND
                        music.status = 1
                )`),
              'playNum'
            ],
            [
              Sequelize.literal(`(
                SELECT COUNT(*)
                FROM collection
                WHERE
                  collection.cid = star.id
                  AND
                  collection.type = 2
                  AND
                  collection.uid = ${ctx.request.header.uid}
              )`),
              'isCollection'
            ],
            [
              Sequelize.literal(`(
                SELECT COUNT(*)
                FROM likes
                WHERE
                    likes.lid = star.id
                    AND
                    likes.type = 2
                    AND
                    likes.uid = ${ctx.request.header.uid}
              )`),
              'isLikes'
            ]
          ],
          exclude: ['isDel','delTime','status'],
        }
      };
      if(!query.pageNO){
        query.pageNO = 1;
      }
      if(!query.pageSize){
        query.pageSize = 10;
      }
      config.offset = (query.pageNO-1)*query.pageSize;
      config.limit = query.pageSize;
      return await ctx.model.Star.findAndCountAll(config);
    }

    // 获取所有歌星播放量
    async getStarPlayNum(){
      const { ctx } = this;
      let config = {
        where:{
          isDel:0,
          status:1
        },
        attributes: {
          include: [
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM album
                  WHERE
                      album.sid = star.id
                      AND
                      album.isDel = 0
                      AND
                      album.status = 1
              )`),
              'albumNum'
            ],
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM music
                  WHERE
                      music.sid = star.id
                      AND
                      music.isDel = 0
                      AND
                      music.status = 1
              )`),
              'musicNum'
            ],
            [
              Sequelize.literal(`(
                    SELECT COUNT(*)
                    FROM collection
                    WHERE
                        collection.cid = star.id
                        AND
                        collection.type = 2
                )`),
              'collectionNum'
            ],
            [
              Sequelize.literal(`(
                    SELECT COUNT(*)
                    FROM likes
                    WHERE
                        likes.lid = star.id
                        AND
                        likes.type = 2
                )`),
              'likesNum'
            ],
            [
              Sequelize.literal(`(
                    SELECT COUNT(*)
                    FROM comment
                    WHERE
                        comment.cid = star.id
                        AND
                        comment.type = 2
                        AND
                        comment.isDel = 0
                        AND
                        comment.status = 1
                )`),
              'commentNum'
            ],
            [
              Sequelize.literal(`(
                    SELECT SUM(music.playNum)
                    FROM music
                    WHERE
                        music.sid = star.id
                        AND
                        music.isDel = 0
                        AND
                        music.status = 1
                )`),
              'playNum'
            ]
          ],
          exclude: ['isDel','delTime'],
        }
      };
      if(ctx.request.header.uid){
        config.attributes.include.push([
          Sequelize.literal(`(
            SELECT COUNT(*)
            FROM collection
            WHERE
              collection.cid = star.id
              AND
              collection.type = 2
              AND
              collection.uid = ${ctx.request.header.uid}
          )`),
          'isCollection'
        ]);
        config.attributes.include.push([
          Sequelize.literal(`(
            SELECT COUNT(*)
            FROM likes
            WHERE
                likes.lid = star.id
                AND
                likes.type = 2
                AND
                likes.uid = ${ctx.request.header.uid}
          )`),
          'isLikes'
        ]);
      }
      return await ctx.model.Star.findAll(config);
    }

    // 全文搜索
    async search(query){
      const { ctx } = this;
      let config = {
        where:{
          isDel:0,
          status:1,
          name:{
            [Op.like]:`%${query.keyword}%`
          }
        },
        attributes: {
          include: [
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM album
                  WHERE
                      album.sid = star.id
                      AND
                      album.isDel = 0
                      AND
                      album.status = 1
              )`),
              'albumNum'
            ],
            [
              Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM music
                  WHERE
                      music.sid = star.id
                      AND
                      music.isDel = 0
                      AND
                      music.status = 1
              )`),
              'musicNum'
            ],
            [
              Sequelize.literal(`(
                    SELECT COUNT(*)
                    FROM collection
                    WHERE
                        collection.cid = star.id
                        AND
                        collection.type = 2
                )`),
              'collectionNum'
            ],
            [
              Sequelize.literal(`(
                    SELECT COUNT(*)
                    FROM likes
                    WHERE
                        likes.lid = star.id
                        AND
                        likes.type = 2
                )`),
              'likesNum'
            ],
            [
              Sequelize.literal(`(
                    SELECT COUNT(*)
                    FROM comment
                    WHERE
                        comment.cid = star.id
                        AND
                        comment.type = 2
                        AND
                        comment.isDel = 0
                        AND
                        comment.status = 1
                )`),
              'commentNum'
            ],
            [
              Sequelize.literal(`(
                    SELECT SUM(music.playNum)
                    FROM music
                    WHERE
                        music.sid = star.id
                        AND
                        music.isDel = 0
                        AND
                        music.status = 1
                )`),
              'playNum'
            ]
          ],
          exclude: ['isDel','delTime'],
        }
      };
      if(ctx.request.header.uid){
        config.attributes.include.push([
          Sequelize.literal(`(
            SELECT COUNT(*)
            FROM collection
            WHERE
              collection.cid = star.id
              AND
              collection.type = 2
              AND
              collection.uid = ${ctx.request.header.uid}
          )`),
          'isCollection'
        ]);
        config.attributes.include.push([
          Sequelize.literal(`(
            SELECT COUNT(*)
            FROM likes
            WHERE
                likes.lid = star.id
                AND
                likes.type = 2
                AND
                likes.uid = ${ctx.request.header.uid}
          )`),
          'isLikes'
        ]);
      }
      return await ctx.model.Star.findAll(config);
    }
  }

  module.exports = StarService;
